import pymysql
from decimal import Decimal
class ConnectMySQl:

    def __init__(self, hostip, user, password, dbname, DBname):
        self.hostip = hostip
        self.user = user
        self.password = password
        self.dbname = dbname
        self.db = pymysql.connect(host=self.hostip, user=self.user, password=self.password, database=self.dbname,
                                  port=3306, charset='utf8')
        self.cursor = self.db.cursor()
        self.dbname = DBname
    # 连接数据库并获取游标
    def connect(self, filename):
        res = []
        # table_is_exist = """
        #     SELECT
        #         *
        #     FROM
        #         information_schema.TABLES
        #     WHERE
        #         TABLE_NAME = (%s)
        # """%(str(filename))
        #
        # # try:
        # self.cursor.execute(table_is_exist)
        # res = self.cursor.fetchall()
        # # except pymysql.err.OperationalError:
        # #     pass


        # 创建数据库
        create_sql = """
           CREATE TABLE %s (
               testid CHAR(20) NOT NULL,
                   class  CHAR(20) NOT NULL,
                   name CHAR(10) NOT NULL,
                      major CHAR(20) NOT NULL,
                      yuwen FLOAT,
                      shuxue FLOAT,
                      yingyu FLOAT,
                      zongfen FLOAT,
                      zfrank INT,
                      zhuan1 FLOAT,
                      zhuan2 FLOAT,
                      zhuan3 FLOAT,
                      zhuanzong FLOAT,
                      allzong FLOAT
                    
         )"""%(filename)
        try:
            self.cursor.execute(create_sql)
        except pymysql.err.OperationalError:
            print("+++++++++++++++++++++++Table:deleting!!!!!++++++++++++++++++++")
            drop_table_sql = """
                DROP TABLE %s
            """%(filename)
            self.cursor.execute(drop_table_sql)
            print("+++++++++++++++++++++++No Table:Creating!!!!!++++++++++++++++++++")
            # 创建数据库
            self.cursor.execute(create_sql)

    def insert_mysql(self, cap):
        skip_first = True
        for stu in cap:
            if skip_first == True:
                skip_first = False
                continue
            testid = str(stu[0])
            class_num = str(stu[1])
            stu_name = str(stu[2])
            stu_major = str(stu[3])

            # -1代表没有成绩，缺考，或者没有该项考试
            if stu[4] == None or type(stu[4]) == str:
                stu_yuwen = -1
            else:
                stu_yuwen = float(stu[4])

            if stu[5] == None or type(stu[5]) == str:
                stu_shuxue = -1
            else:
                stu_shuxue = float(stu[5])

            if stu[6] == None or type(stu[6]) == str:
                stu_yingyu = -1
            else:
                stu_yingyu = float(stu[6])

            if stu[7] == None or type(stu[7]) == str:
                stu_zongfen = -1
            else:
                stu_zongfen = float(stu[7])

            if stu[8] == None or type(stu[8]) == str:
                stu_zfrank = -1
            else:
                stu_zfrank = int(stu[8])

            if stu[9] == None or type(stu[9]) == str:
                stu_zhuan1 = -1
            else:
                stu_zhuan1 = float(stu[9])

            if stu[10] == None or type(stu[10]) == str:
                stu_zhuan2 = -1
            else:
                stu_zhuan2 = float(stu[10])

            if stu[11] == None or type(stu[11]) == str:
                stu_zhuan3 = -1
            else:
                stu_zhuan3 = float(stu[11])

            if  stu[12] == None or type(stu[12]) == str:
                stu_zhuanzong = -1
            else:
                stu_zhuanzong = float(stu[12])

            if stu[13] == None or type(stu[13]) == str:
                stu_allzong = -1
            else:
                stu_allzong = float(stu[13])



            self.cursor.execute(
                f"insert into %s"%(self.dbname)+" (testid,class,name,major,yuwen,shuxue,yingyu,zongfen,zfrank,zhuan1,zhuan2,zhuan3,zhuanzong,allzong) "
                f"value"
                f" ('{testid}','{class_num}','{stu_name}','{stu_major}','{stu_yuwen}','{stu_shuxue}','{stu_yingyu}','{stu_zongfen}','{stu_zfrank}','{stu_zhuan1}','{stu_zhuan2}','{stu_zhuan3}','{stu_zhuanzong}','{stu_allzong}')")
        self.db.commit()

    def calculate_ysw_average(self, tmp_list_list):
        last_list = []
        yuwen_dict = {}
        shuxue_dict = {}
        yingyu_dict = {}
        class_list = []
        for i in tmp_list_list:
           if i[0] in class_list:
               pass
           else:
               class_list.append(i[0])

        for i in class_list:
            sql = "select AVG(yuwen) from %s" % self.dbname + " where class = (%s) and yuwen>=0"
            self.cursor.execute(sql, (i))
            res = self.cursor.fetchone()
            yuwen_dict[i] = float(Decimal(res[0]).quantize(Decimal("0.1"), rounding="ROUND_HALF_UP"))

            sql = "select AVG(shuxue) from %s" % self.dbname + " where class = (%s) and shuxue>=0"
            self.cursor.execute(sql, (i))
            res = self.cursor.fetchone()
            shuxue_dict[i] = float(Decimal(res[0]).quantize(Decimal("0.1"), rounding="ROUND_HALF_UP"))

            sql = "select AVG(yingyu) from %s" % self.dbname + " where class = (%s) and yingyu>=0"
            self.cursor.execute(sql, (i))
            res = self.cursor.fetchone()
            yingyu_dict[i] = float(Decimal(res[0]).quantize(Decimal("0.1"), rounding="ROUND_HALF_UP"))

        last_list.append(yuwen_dict)
        last_list.append(shuxue_dict)
        last_list.append(yingyu_dict)
        print("+++++++++++++++++++++各班级文化课成绩+++++++++++++++++++++")
        print(last_list)
        return last_list

    def class_major_sql(self):
        class_major_list_list = []
        sql = "select distinct class,major from %s" % self.dbname
        self.cursor.execute(sql)
        res = self.cursor.fetchall()
        for i in res:
            tmp_class_major = []
            tmp_class_major.append(i[0])
            tmp_class_major.append(i[1])
            if tmp_class_major in class_major_list_list:
                pass
            else:
                class_major_list_list.append(tmp_class_major)
        return class_major_list_list

    def calculate_major_average(self, tmp_list_list):
        last_list = []
        zhuan1_dict = {}
        zhuan2_dict = {}
        zhuan3_dict = {}
        zhuanzong_dict = {}
        for class_major in tmp_list_list:
            # print(class_major)
            sql = "select AVG(zhuan1) from %s" % self.dbname+" where class = (%s) and zhuan1 >= 0 and major = (%s)"
            self.cursor.execute(sql, (class_major[0], class_major[1]))
            res = self.cursor.fetchone()
            if res[0] == None:
                zhuan1_dict[class_major[0]+class_major[1]] = -1
            else:
                zhuan1_dict[class_major[0]+class_major[1]] = float(Decimal(res[0]).quantize(Decimal("0.1"), rounding="ROUND_HALF_UP"))

            sql = "select AVG(zhuan2) from %s" % self.dbname+" where class = (%s) and zhuan2 >= 0 and major = (%s)"
            self.cursor.execute(sql, (class_major[0], class_major[1]))
            res = self.cursor.fetchone()
            if res[0] == None:
                zhuan2_dict[class_major[0]+class_major[1]] = -1
            else:
                zhuan2_dict[class_major[0]+class_major[1]] = float(Decimal(res[0]).quantize(Decimal("0.1"), rounding="ROUND_HALF_UP"))

            sql = "select AVG(zhuan3) from %s" % self.dbname+" where class = (%s) and zhuan3 >= 0 and major = (%s)"
            self.cursor.execute(sql, (class_major[0], class_major[1]))
            res = self.cursor.fetchone()
            if res[0] == None:
                zhuan3_dict[class_major[0]+class_major[1]] = -1
            else:
                zhuan3_dict[class_major[0]+class_major[1]] = float(Decimal(res[0]).quantize(Decimal("0.1"), rounding="ROUND_HALF_UP"))

            sql = "select AVG(zhuanzong) from %s" % self.dbname + " where class = (%s) and zhuanzong >= 0 and major = (%s)"
            self.cursor.execute(sql, (class_major[0], class_major[1]))
            res = self.cursor.fetchone()
            if res[0] == None:
                zhuanzong_dict[class_major[0] + class_major[1]] = -1
            else:
                zhuanzong_dict[class_major[0] + class_major[1]] = float(
                    Decimal(res[0]).quantize(Decimal("0.1"), rounding="ROUND_HALF_UP"))

        last_list.append(zhuanzong_dict)
        last_list.append(zhuan1_dict)
        last_list.append(zhuan2_dict)
        last_list.append(zhuan3_dict)
        print("+++++++++++++++++++++各班级专业课成绩+++++++++++++++++++++")
        print(last_list)
        return last_list

    def calculate_yxw_50_100(self, tmp_list_list, ranknum):
        last_rank_list = []
        yuwen100dict = {}
        shuxue100dict = {}
        yingyu100dict = {}
        allrankdict= {}

        sql1 = """
            SELECT
                 COUNT(*)
            FROM
                (
                    SELECT
                        class,
                        name,
                        major,
                        yuwen,
                        RANK() OVER(
                            ORDER BY yuwen DESC
                        ) yuwenXIAOrank
                    FROM
                        %s""" % self.dbname + """
                   
                )  AS yuwenRankTmp
            WHERE
                yuwenXIAOrank <= (%s)
                AND
                yuwenXIAOrank >= 0
                AND
                class = (%s)
        """



        sql2 = """
            SELECT
                 COUNT(*)
            FROM
                (
                    SELECT
                        class,
                        name,
                        major,
                        shuxue,
                        RANK() OVER(
                            ORDER BY shuxue DESC
                        ) shuxueXIAOrank
                    FROM
                        %s""" % self.dbname + """
                   
                )  AS shuxueRankTmp
            WHERE
                shuxueXIAOrank <= (%s)
                AND
                shuxueXIAOrank >= 0
                AND
                class = (%s)
        """

        sql3 = """
                  SELECT
                       COUNT(*)
                  FROM
                      (
                          SELECT
                              class,
                              name,
                              major,
                              yingyu,
                              RANK() OVER(
                                  ORDER BY yingyu DESC
                              ) yingyuXIAOrank
                          FROM
                             %s""" % self.dbname + """

                      )  AS yingyuRankTmp
                  WHERE
                      yingyuXIAOrank <= (%s)
                      AND
                      yingyuXIAOrank >= 0
                      AND
                      class = (%s)
              """

        sql4 = """
                  SELECT
                       COUNT(*)
                  FROM
                       %s""" % self.dbname + """
                  WHERE
                      zfrank <= (%s)
                      AND
                      zfrank >= 0
                      AND
                      class = (%s)
              """




        onlyclass= []
        for class_major in tmp_list_list:
            if class_major[0] in onlyclass:
                pass
            else:
                onlyclass.append(class_major[0])

        for i in onlyclass:
            self.cursor.execute(sql1, (ranknum, i))
            res1 = self.cursor.fetchall()
            yuwen100dict[i] = res1[0][0]

            self.cursor.execute(sql2, (ranknum, i))
            res1 = self.cursor.fetchall()
            shuxue100dict[i] = res1[0][0]

            self.cursor.execute(sql3, (ranknum, i))
            res1 = self.cursor.fetchall()
            yingyu100dict[i] = res1[0][0]

            self.cursor.execute(sql4, (ranknum, i))
            res1 = self.cursor.fetchall()
            allrankdict[i] = res1[0][0]

        last_rank_list.append(allrankdict)
        last_rank_list.append(yuwen100dict)
        last_rank_list.append(shuxue100dict)
        last_rank_list.append(yingyu100dict)

        print("+++++++++++++++++++++各班级专业前50/100r人数+++++++++++++++++++++")
        print(last_rank_list)
        return last_rank_list


    #计算各班级人数，求其三分之一人数，计算总分班内的前三分之一，和专业总分的前三分之一
    def calculate_match(self,tmp_list_list):
        class_major_student_count_dict = {}
        # 获取班级人数，采用获取总分的计数方式
        sql_get_student_count = """
                   SELECT
                       COUNT(zhuanzong)
                   FROM 
                        %s""" % self.dbname + """
                   WHERE
                       class = (%s)
                       AND
                       major = (%s)
                       AND
                       zhuanzong >= 0
               """

        # zhuanzong或者allzong
        # class
        # major
        # 只求前三分之一的人
        sql_zhuanzong_rank_3_1 = """
                    SELECT
                        name,zhuanzong_rank
                    FROM
                        (
                            SELECT
                                name,
                                RANK() OVER(
                                    PARTITION BY class,major
                                    ORDER BY zhuanzong DESC
                                )zhuanzong_rank
                            FROM 
                                 %s""" % self.dbname + """
                            WHERE
                                class = (%s)
                                AND
                                major = (%s)
                                AND
                                zhuanzong >= 0
                        ) AS zhuanzongRankTmp
                    WHERE
                        zhuanzong_rank <= (%s)   
                """

        sql_allzong_rank_3_1 = """
                    SELECT
                             name,allzong_rank
                         FROM
                             (
                                 SELECT
                                     name,
                                     RANK() OVER(
                                         PARTITION BY class,major
                                         ORDER BY allzong DESC
                                     )allzong_rank
                                 FROM 
                                     %s""" % self.dbname + """
                                 WHERE
                                     class = (%s)
                                     AND
                                     major = (%s)
                                     AND
                                     allzong >= 0
                             ) AS allzongRankTmp
                         WHERE
                             allzong_rank <= (%s)   
                     """
        match_dict = {}
        for class_major in tmp_list_list:
            zhuanzong_rank_list = []
            allzong_rank_list = []
            zhuanzong_rank_list.clear()
            allzong_rank_list.clear()

            match_count = 0
            self.cursor.execute(sql_get_student_count, (class_major[0], class_major[1]))
            res = self.cursor.fetchone()
            student_count = int(Decimal(res[0]).quantize(Decimal("1"), rounding="ROUND_HALF_UP") / 3)
            class_major_student_count_dict[class_major[0] + class_major[1]] = student_count

            # 求出人数后，将符合条件排名的人数求出来，放进列表中
            # 采用两个列表，list1放总分前三分之一，list2放专业总分三分之一
            # 遍历list2元素在list1中是否存
            self.cursor.execute(sql_zhuanzong_rank_3_1, (class_major[0], class_major[1], student_count))
            res = self.cursor.fetchall()
            for i in res:
                zhuanzong_rank_list.append(i[0])


            self.cursor.execute(sql_allzong_rank_3_1,(class_major[0], class_major[1], student_count))
            res = self.cursor.fetchall()
            for i in res:
                allzong_rank_list.append(i[0])

            for i in zhuanzong_rank_list:
                if i in allzong_rank_list:
                    match_count += 1
                else:
                    pass

            match_rate = float(Decimal(match_count/student_count).quantize(Decimal("0.01"), rounding="ROUND_HALF_UP"))
            match_dict[class_major[0]+class_major[1]] = match_rate
        print("+++++++++++++++++++++各班级专业匹配率+++++++++++++++++++++")
        print(match_dict)
        return match_dict


    def get_class_average(self):
        average_dict = {}
        sqlyuwen = "select AVG(yuwen) from  %s" % self.dbname + " where yuwen >= 0"
        sqlshuxue = "select AVG(shuxue) from  %s" % self.dbname + " where shuxue >= 0"
        sqlyingyu= "select AVG(yingyu) from  %s" % self.dbname + " where yingyu >= 0"

        # 计算语文平均分
        self.cursor.execute(sqlyuwen)
        res = self.cursor.fetchone()
        res = float(Decimal(res[0]).quantize(Decimal("0.1"), rounding="ROUND_HALF_UP"))
        average_dict["yuwen"] = res

        # 计算数学平均分
        self.cursor.execute(sqlshuxue)
        res = self.cursor.fetchone()
        res = float(Decimal(res[0]).quantize(Decimal("0.1"), rounding="ROUND_HALF_UP"))
        average_dict["shuxue"] = res

        # 计算外语平均分
        self.cursor.execute(sqlyingyu)
        res = self.cursor.fetchone()
        res = float(Decimal(res[0]).quantize(Decimal("0.1"), rounding="ROUND_HALF_UP"))
        average_dict["yingyu"] = res


        print(average_dict)
        return average_dict




    def close_sql(self):
        self.cursor.close()
        self.db.close()
